<?php

/**
 * @version		$Id: hello.php 15 2009-11-02 18:37:15Z chdemko $
 * @package		Joomla16.Tutorials
 * @subpackage	Components
 * @copyright	Copyright (C) 2005 - 2010 Open Source Matters, Inc. All rights reserved.
 * @author		Christophe Demko
 * @link		http://joomlacode.org/gf/project/helloworld_1_6/
 * @license		License GNU General Public License version 2 or later
 */

// No direct access to this file
defined('_JEXEC') or die('Restricted access');

// import Joomla modelitem library
jimport('joomla.application.component.modelitem');

/**
 * HelloWorld Model
 */
class bgModelbg extends JModel
{
	var $_total = null;
    var $_pagination = null;
    
    public function __construct(){
    	parent::__construct();
        $mainframe = JFactory::getApplication();
        // Get pagination request variables
        $limit = $mainframe->getUserStateFromRequest('com_frquestion.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
        $limitstart = JRequest::getVar('limitstart', 0, '', 'int');
 
        // In case limit has been changed, adjust it
        $limitstart = ($limit != 0 ? (floor($limitstart / $limit) * $limit) : 0);
        $this->setState('limit', $limit);
        $this->setState('limitstart', $limitstart);
    }

    /**
     * Remove RESQ from RESQ_SESS_CODE 
    */
    public function removeresq($serq_code,$user_code){
        $query = "DELETE FROM resq
                        WHERE RESQ_SESS_CODE IN (
                                    SELECT  SESS_CODE
                                    FROM    sess
                                    WHERE   SESS_SERQ_CODE IN($serq_code) AND SESS_USER_CODE IN($user_code))";
        
        $this->_db->setQuery($query);
        $this->_db->query();
    }

    /**
     * Remove SESS from SESS_SERQ_CODE 
    */
    public function removesess($serq_code,$user_code){    
        $query = "DELETE FROM sess WHERE SESS_SERQ_CODE IN($serq_code) AND SESS_USER_CODE IN($user_code)";

        $this->_db->setQuery($query);
        $this->_db->query();
    }

    /**
     * Save Session
    **/
    public function saveSessInsert($data){
    	$sql = "INSERT INTO sess (SESS_CODE
                                ,SESS_USER_CODE
                                ,SESS_SERQ_CODE
                                ,SESS_IP
                                ,SESS_PLAY_TIME
                                ,SESS_COMPLETED
                                ,SESS_PUBLISHED) 
                VALUES ($data[SESS_CODE]
                  , $data[SESS_USER_CODE]
                  , $data[SESS_SERQ_CODE]
                  , '$data[SESS_IP]'
                  , '$data[SESS_PLAY_TIME]'
                  , $data[SESS_COMPLETED]
                  , $data[SESS_PUBLISHED])";
        
        $this->_db->setQuery($sql);       
        if($this->_db->query()){
            return true;
        }else{
            return false;
        }
    }
    
    /**
     * Save Session
    **/
    public function saveSessUpdate($data){
    	$sql = "UPDATE sess
                        SET SESS_USER_CODE = $data[SESS_USER_CODE]
                        ,SESS_SERQ_CODE = $data[SESS_SERQ_CODE]
                        ,SESS_IP = '$data[SESS_IP]'
                        ,SESS_PLAY_TIME = '$data[SESS_PLAY_TIME]'
                        ,SESS_COMPLETED = $data[SESS_COMPLETED]
                        ,SESS_PUBLISHED = $data[SESS_PUBLISHED]
                        WHERE SESS_CODE = $data[SESS_CODE]";
        
        $this->_db->setQuery($sql);       
        if($this->_db->query()){
            return true;
        }else{
            return false;
        }
    }
    
    /**
     * Save Result Question
    **/
    public function saveResqInsert($data){
    	$sql = "INSERT INTO resq (RESQ_CODE,RESQ_QUES_CODE,RESQ_SESS_CODE,RESQ_VALUE,RESQ_TYPE) 
                VALUES ";
        
        $val = "(".$data[0]['RESQ_CODE'].",".$data[0]['RESQ_QUES_CODE'].",".$data[0]['RESQ_SESS_CODE'].","."'".$data[0]['RESQ_VALUE']."'".",".$data[0]['RESQ_TYPE'].")";
        
        for($i = 1; $i < count($data); $i++){
            
            if($data[$i] != null && $data[$i]['RESQ_CODE'] != null){
                $val .= ", (".$data[$i]['RESQ_CODE'].",".$data[$i]['RESQ_QUES_CODE'].",".$data[$i]['RESQ_SESS_CODE'].","."'".$data[$i]['RESQ_VALUE']."'".",".$data[$i]['RESQ_TYPE'].")";
            }
        }
        //create query
        $sql .= $val;
        
        //save database
        $this->_db->setQuery($sql);       
        if($this->_db->query()){
            return true;
        }else{
            return false;
        }
    }
    /**
     * Save Result Question
    **/
    public function saveResqUpdate($data){
        /* UPDATE RESQ */
        if($data != null){
            for($i = 0; $i < count($data); $i++){
                $sql = "UPDATE resq
                        SET   RESQ_QUES_CODE = ".$data[$i]['RESQ_QUES_CODE'].",RESQ_SESS_CODE = ".$data[$i]['RESQ_SESS_CODE'].",RESQ_VALUE = '".$data[$i]['RESQ_VALUE']."',RESQ_TYPE = ".$data[$i]['RESQ_TYPE']."
                        WHERE RESQ_CODE = ".$data[$i]['RESQ_CODE'];
                
                $this->_db->setQuery($sql);
                $this->_db->query();
            }
        }
    }
    
    /**
     * Delete Result Question From Sess_code and Ques_code
    **/
    public function removeResqFrSessQuess($data){
        /* UPDATE RESQ */
        if($data != null){
            for($i = 0; $i < count($data); $i++){
                $sql = "DELETE FROM resq
                        WHERE RESQ_SESS_CODE = ".$data[$i]['SESS_CODE']." AND RESQ_QUES_CODE = ".$data[$i]['QUES_CODE'];
                
                $this->_db->setQuery($sql);
                $this->_db->query();
            }
        }
    }
    
    /**
	*Get Total
	*/
    function getTotal($where = null){
        //Load the content if it doesn't already exist
        $dt_now = date('Y-m-d');
        $user = &JFactory::getUser();
        
        if($this->_total === null){
            /*$query = "SELECT COUNT(DISTINCT *) 
                            FROM serq 
                            WHERE SERQ_FLAG = 1 
                            AND SERQ_SEND_USER_ID_LIST LIKE '%(".$user->id.")%' 
                            AND SERQ_START <= '$dt_now' AND SERQ_FINISH >= '$dt_now' 
                            AND $user->id NOT IN( SELECT SESS_USER_CODE FROM sess WHERE SESS_USER_CODE = $user->id  AND SESS_SERQ_CODE = SERQ_CODE) ORDER BY SERQ_CODE ASC";*/
            $query = "SELECT COUNT(DISTINCT *) 
                            FROM serq 
                            WHERE SERQ_FLAG = 1 
                            AND SERQ_SEND_USER_ID_LIST LIKE '%(".$user->id.")%' 
                            AND SERQ_START <= '$dt_now' AND SERQ_FINISH >= '$dt_now' 
                            ORDER BY SERQ_CODE ASC";
            $this->_db->setQuery($query);
            $this->_total = $this->_db->loadResult();
        }
        return $this->_total;
    }
    
    /**
	*Get Pagination
	*/
    function getPagination(){
        // Load the content if it doesn't already exist
        if (empty($this->_pagination)) {
            jimport('joomla.html.pagination');
            $this->_pagination = new JPagination($this->getTotal(), $this->getState('limitstart'), $this->getState('limit') );
        }
        return $this->_pagination;
    }
    
    /**
     * Get list question
    **/
	function getAllQuestionar($list = null){
	   
       $user = &JFactory::getUser();
       $dt_now = date('Y-m-d');
       
       /*$qr = "SELECT DISTINCT SERQ_CODE,SERQ_TITLE,SERQ_START,SERQ_FINISH 
              FROM serq 
              WHERE SERQ_FLAG = 1 
              AND SERQ_SEND_USER_ID_LIST LIKE '%(".$user->id.")%' 
              AND SERQ_START <= '$dt_now' AND SERQ_FINISH >= '$dt_now' 
              AND $user->id NOT IN( SELECT SESS_USER_CODE FROM sess WHERE SESS_USER_CODE = $user->id AND SESS_SERQ_CODE = SERQ_CODE) ";*/
       
       $qr = "SELECT DISTINCT SERQ_CODE,SERQ_TITLE,SERQ_START,SERQ_FINISH 
              FROM serq 
              WHERE SERQ_FLAG = 1 
              AND SERQ_PUBLISHED = 1
              AND SERQ_SEND_USER_ID_LIST LIKE '%(".$user->id.")%' 
              AND SERQ_START <= '$dt_now' AND SERQ_FINISH >= '$dt_now' ";

       //search
       $where = array();
       if(!empty($list['SERQ_TITLE_SEARCH'])){
            $where[] = "SERQ_TITLE LIKE '%$list[SERQ_TITLE_SEARCH]%'";
       }
       if(!empty($list['SERQ_START_SEARCH'])){
            $where[] = "SERQ_START <= '$list[SERQ_START_SEARCH]'";
       }
       if(!empty($list['SERQ_FINISH_SEARCH'])){
            $where[] = "SERQ_FINISH >= '$list[SERQ_FINISH_SEARCH]'";
       }
       
       if($where){
            $where = " AND ". implode(' AND ', $where);
       }else{
            $where = null;
       }
       // get total number record
       $this->_total = $this->getTotal($where);
         
       $qr .= $where;
       
       // order by
       $qr .= " ORDER BY SERQ_CODE ASC"; 
       //print_r($qr);
       // pagination
       $qr .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');
       
       // load data
       $this->_db->setQuery($qr);
       $result_serq = $this->_db->loadObjectList();
       
       return $result_serq;
	}
    
    /**
     * Get list Ansq 
    **/
    public function check_get_sess(){
      $user = &JFactory::getUser();
        $qr = "SELECT DISTINCT SESS_SERQ_CODE
                FROM sess 
                WHERE SESS_USER_CODE = $user->id ";
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       return $result;
    }

    /**
     * Get info questionar
    **/
	function getQuestionarDetail($cid){

       $qr = "SELECT SERQ_CODE,SERQ_TITLE,SERQ_DESCRIPTION,SERQ_IMG
                FROM serq 
                WHERE SERQ_CODE = $cid";
       
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();
       
       return $result;
	}
    
    /**
     * Get info questionar when end answer
    **/
	function getQuestionarEnd($cid){

       $qr = "SELECT SERQ_CODE,SERQ_TITLE,SERQ_END_TITLE,SERQ_END_DESCRIPTION,SERQ_IMG
                FROM serq 
                WHERE SERQ_CODE = $cid";
       
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();
       
       return $result;
	}
    /**
     * Get min page from list page_code, where serq_code = id
    **/
    function getMinPage($serq_code){
        $qr = "SELECT MIN(DISTINCT PAGQ_CODE)
                    FROM ques
                    LEFT JOIN serq ON SERQ_CODE = QUES_SERQ_CODE
                    LEFT JOIN pagq ON PAGQ_CODE = QUES_PAGQ_CODE
                    WHERE SERQ_CODE = $serq_code
                    ORDER BY PAGQ_CODE ASC";
        //load data
        $this->_db->setQuery($qr);
        $result = $this->_db->loadResult();
        return $result;
    }
    /**
     * Get list page from serq_code
    **/
    function getListPage($serq_code){
        $qr = "SELECT DISTINCT PAGQ_CODE
                    FROM ques
                    LEFT JOIN serq ON SERQ_CODE = QUES_SERQ_CODE
                    LEFT JOIN pagq ON PAGQ_CODE = QUES_PAGQ_CODE
                    WHERE SERQ_CODE = $serq_code
                    ORDER BY PAGQ_CODE ASC";
        //load data
        $this->_db->setQuery($qr);
        $result = $this->_db->loadObjectList();
        $arr = array();
        for($i = 0; $i < count($result); $i++){
            $arr[] = $result[$i]->PAGQ_CODE;
        }
        return $arr;
        
    }
    /**
     * Get list question
    **/
    function getListQuestion($cid,$page){

       $qr = "SELECT DISTINCT QUES_CODE,QUES_TITLE,QUES_TYPE,QUES_DESCRIPTION,QUES_REQUIRED,PAGQ_CODE,PAGQ_TITLE,PAGQ_DESCRIPTION
                FROM ques 
                LEFT JOIN serq ON SERQ_CODE = QUES_SERQ_CODE
                LEFT JOIN pagq ON PAGQ_CODE = QUES_PAGQ_CODE
                WHERE SERQ_CODE = $cid AND PAGQ_CODE = $page 
                ORDER BY QUES_CODE ASC ";
       
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       return $result;
	}
    /**
     * Get list Ansq 
    **/
    function getListAnsq($cid){
        $qr = "SELECT DISTINCT ANSQ_CODE,ANSQ_QUES_CODE,ANSQ_VALUE 
                FROM ansq 
                LEFT JOIN ques ON QUES_CODE = ANSQ_QUES_CODE
                WHERE QUES_SERQ_CODE = $cid 
                ORDER BY ANSQ_CODE ASC ";
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       return $result;
    }
    
    /**
     * Get list Result Question
    **/
    function getListResult($sess_code){
        $qr = "SELECT RESQ_CODE,RESQ_QUES_CODE,RESQ_SESS_CODE,RESQ_VALUE,RESQ_TYPE 
                FROM resq 
                WHERE RESQ_SESS_CODE = $sess_code";
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       return $result;
    }
    
    /* merge 2 sql */
    function getListQuestion_Temp($cid,$page){

       $qr = "SELECT DISTINCT QUES_CODE,QUES_TITLE,QUES_TYPE,QUES_DESCRIPTION,QUES_REQUIRED,PAGQ_CODE,PAGQ_TITLE,PAGQ_DESCRIPTION
                FROM ques 
                LEFT JOIN serq ON SERQ_CODE = QUES_SERQ_CODE
                LEFT JOIN pagq ON PAGQ_CODE = QUES_PAGQ_CODE
                WHERE SERQ_CODE = $cid AND PAGQ_CODE = $page";
       
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       // get list answer for QUESTION
       $qr2 = "SELECT ANSQ_CODE,ANSQ_QUES_CODE,ANSQ_VALUE 
                FROM ansq 
                LEFT JOIN ques ON QUES_CODE = ANSQ_QUES_CODE
                WHERE QUES_SERQ_CODE = $cid";
       // load data
       $this->_db->setQuery($qr2);
       $ansq = $this->_db->loadObjectList();
                     
       for($i = 0; $i < count($result); $i++){
            $ansq_arr = array();
            for($j = 0; $j < count($ansq); $j++){
                if($result[$i]->QUES_CODE == $ansq[$j]->ANSQ_QUES_CODE){
                    $ansq_arr[] = $ansq[$j];
                }
            }
            if($ansq_arr != null){
                $result[$i]->ANSQ_ARR = $ansq_arr;
            }else{
                $result[$i]->ANSQ_ARR = null;
            }
            
       }
       //echo "<pre>";print_r($result);echo "</pre>";exit();
       return $result;
	}
	function getListQuestion_UnUse($cid,$limitstart,$limit){

       $qr = "SELECT DISTINCT QUES_CODE,QUES_TITLE,QUES_TYPE,QUES_DESCRIPTION,QUES_REQUIRED,PAGQ_CODE,PAGQ_TITLE,PAGQ_DESCRIPTION
                FROM ques 
                LEFT JOIN serq ON SERQ_CODE = QUES_SERQ_CODE
                LEFT JOIN pagq ON PAGQ_CODE = QUES_PAGQ_CODE
                WHERE SERQ_CODE = $cid 
                LIMIT $limitstart,$limit";
       
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       // get list answer for QUESTION
       $qr2 = "SELECT ANSQ_CODE,ANSQ_QUES_CODE,ANSQ_VALUE 
                FROM ansq 
                LEFT JOIN ques ON QUES_CODE = ANSQ_QUES_CODE
                WHERE QUES_SERQ_CODE = $cid";
       // load data
       $this->_db->setQuery($qr2);
       $ansq = $this->_db->loadObjectList();
                     
       for($i = 0; $i < count($result); $i++){
            $ansq_arr = array();
            for($j = 0; $j < count($ansq); $j++){
                if($result[$i]->QUES_CODE == $ansq[$j]->ANSQ_QUES_CODE){
                    $ansq_arr[] = $ansq[$j];
                }
            }
            if($ansq_arr != null){
                $result[$i]->ANSQ_ARR = $ansq_arr;
            }else{
                $result[$i]->ANSQ_ARR = null;
            }
            
       }
       //echo "<pre>";print_r($result);echo "</pre>";exit();
       return $result;
	}
    
    /**
     * Get total record question
    **/
    function getTotalQuestion($cid){
        if($this->_total === null){
            $query = "SELECT count(DISTINCT QUES_CODE)
                        FROM ques 
                        LEFT JOIN serq ON SERQ_CODE = QUES_SERQ_CODE
                        WHERE SERQ_CODE = $cid";
            $this->_db->setQuery($query);
            $total = $this->_db->loadResult();
        }
        return $total;
    }
    
    
}
